<!DOCTYPE html><html><head><title>MySQL学习笔记（Day013：作业讲解一/Rank/视图/UNION/触发器）</title><meta charset='utf-8'><link href='https://dn-maxiang.qbox.me/res-min/themes/marxico.css' rel='stylesheet'><style>

.note-content  {font-family: 'Helvetica Neue', Arial, 'Hiragino Sans GB', STHeiti, 'Microsoft YaHei', 'WenQuanYi Micro Hei', SimSun, Song, sans-serif;}
</style></head><body><div id='preview-contents' class='note-content'>
                        <div id="wmd-preview" class="preview-content"></div>
                    <div id="wmd-preview-section-1" class="wmd-preview-section preview-content">

</div><div id="wmd-preview-section-375" class="wmd-preview-section preview-content">

<h1 id="mysql学习笔记day013作业讲解一rank视图union触发器">MySQL学习笔记（Day013：作业讲解一/Rank/视图/UNION/触发器）</h1>

<p><p class="note-tags "><code class="notebook">MySQL学习</code> </p></p>

<div><div class="toc"><div class="toc">
<ul>
<li><a href="#mysql学习笔记day013作业讲解一rank视图union触发器">MySQL学习笔记（Day013：作业讲解一/Rank/视图/UNION/触发器）</a><ul>
<li><a href="#一-作业讲解">一. 作业讲解</a></li>
<li><a href="#二-rank">二. Rank</a></li>
<li><a href="#三-视图">三. 视图</a></li>
<li><a href="#四-union">四. UNION</a></li>
<li><a href="#五-触发器">五. 触发器</a></li>
</ul>
</li>
</ul>
</div>
</div>
</div></div><div id="wmd-preview-section-7199" class="wmd-preview-section preview-content">

<h2 id="一-作业讲解">一. 作业讲解</h2>

<ul><li><p><strong>查询employees表中非基层用户的最近详细信息</strong></p>

<blockquote>
  <p>老师的讲解的版本中存在问题，重新作为作业</p>
</blockquote></li>
<li><p><strong>统计dbt3库下orders每周每个客户的订单数量</strong></p>

<ol>
<li rel="1"><p>思路</p>

<ul>
<li><p>找到订单中最小周(week)之前的一周的周一，这里进行了简化，使用了<code>1970-01-05</code>作为周一标记，作为起始(<code>start</code>)</p>

<pre class="prettyprint hljs-dark"><code class="language-bash hljs"><span class="hljs-comment line-number">1.</span>[root@MyServer ~]&gt; cal <span class="hljs-number">1</span> <span class="hljs-number">1970</span><br><span class="hljs-comment line-number">2.</span>    January <span class="hljs-number">1970</span>    <br><span class="hljs-comment line-number">3.</span>Su Mo Tu We Th Fr Sa<br><span class="hljs-comment line-number">4.</span>             <span class="hljs-number">1</span>  <span class="hljs-number">2</span>  <span class="hljs-number">3</span><br><span class="hljs-comment line-number">5.</span> <span class="hljs-number">4</span>  <span class="hljs-number">5</span>  <span class="hljs-number">6</span>  <span class="hljs-number">7</span>  <span class="hljs-number">8</span>  <span class="hljs-number">9</span> <span class="hljs-number">10</span>  <span class="hljs-comment"># 1970-01-05 刚好是周一，用1月12号，19号，26号等也是可以的</span><br><span class="hljs-comment line-number">6.</span><span class="hljs-number">11</span> <span class="hljs-number">12</span> <span class="hljs-number">13</span> <span class="hljs-number">14</span> <span class="hljs-number">15</span> <span class="hljs-number">16</span> <span class="hljs-number">17</span><br><span class="hljs-comment line-number">7.</span><span class="hljs-number">18</span> <span class="hljs-number">19</span> <span class="hljs-number">20</span> <span class="hljs-number">21</span> <span class="hljs-number">22</span> <span class="hljs-number">23</span> <span class="hljs-number">24</span><br><span class="hljs-comment line-number">8.</span><span class="hljs-number">25</span> <span class="hljs-number">26</span> <span class="hljs-number">27</span> <span class="hljs-number">28</span> <span class="hljs-number">29</span> <span class="hljs-number">30</span> <span class="hljs-number">31</span><br></code></pre></li>
<li><p>在起始条件周一（<code>start</code>)的基础上 <code>增加6天</code>时间，就是周日，即为一周的结束标记(<code>end</code>)</p></li>
<li>通过对<code>start</code>（周一）、<code>end</code>（周日）以及<code>o_custkey</code>进行<code>分组</code>，使用<code>count(o_orderkey)</code>得到对应的数量</li></ul></li>
<li rel="3"><p>SQL语句</p>

<pre class="prettyprint hljs-dark"><code class="language-sql hljs"><span class="hljs-comment line-number">1.</span><span class="hljs-comment">-- 最终结果</span><br><span class="hljs-comment line-number">2.</span><span class="hljs-operator"><span class="hljs-keyword">select</span> o_custkey,<br><span class="hljs-comment line-number">3.</span>ADDDATE(<span class="hljs-string">'1970-01-05'</span>, <span class="hljs-built_in">INTERVAL</span> <span class="hljs-keyword">FLOOR</span>(<span class="hljs-keyword">DATEDIFF</span>(o_orderdate, <span class="hljs-string">'1970-01-05'</span>)/<span class="hljs-number">7</span>)*<span class="hljs-number">7</span> <span class="hljs-keyword">Day</span>) <span class="hljs-keyword">as</span> <span class="hljs-keyword">start</span>,<br><span class="hljs-comment line-number">4.</span>ADDDATE(<span class="hljs-string">'1970-01-05'</span>, <span class="hljs-built_in">INTERVAL</span> <span class="hljs-keyword">FLOOR</span>(<span class="hljs-keyword">DATEDIFF</span>(o_orderdate, <span class="hljs-string">'1970-01-05'</span>)/<span class="hljs-number">7</span>)*<span class="hljs-number">7</span> + <span class="hljs-number">6</span> <span class="hljs-keyword">Day</span>) <span class="hljs-keyword">as</span> <span class="hljs-keyword">end</span>,<br><span class="hljs-comment line-number">5.</span><span class="hljs-keyword">count</span>(o_orderkey) <span class="hljs-keyword">as</span> total <br><span class="hljs-comment line-number">6.</span><span class="hljs-keyword">from</span> dbt3.orders <span class="hljs-keyword">group</span> <span class="hljs-keyword">by</span> o_custkey, <span class="hljs-keyword">start</span>, <span class="hljs-keyword">end</span>;</span><br><span class="hljs-comment line-number">7.</span><br><span class="hljs-comment line-number">8.</span><span class="hljs-comment">-- DATEDIFF(o_orderdate, '1970-01-05')</span><br><span class="hljs-comment line-number">9.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> <span class="hljs-keyword">datediff</span>(<span class="hljs-string">'1971-01-01'</span>, <span class="hljs-string">'1970-01-05'</span>);</span>  <span class="hljs-comment">-- 随意取一个1971-01-01，作为演示</span><br><span class="hljs-comment line-number">10.</span>+<span class="hljs-comment">--------------------------------------+</span><br><span class="hljs-comment line-number">11.</span>| datediff('1971-01-01', '1970-01-05') |<br><span class="hljs-comment line-number">12.</span>+<span class="hljs-comment">--------------------------------------+</span><br><span class="hljs-comment line-number">13.</span>|                                  361 |  <span class="hljs-comment">-- 1971-01-01 减去 1970-01-5 为361天</span><br><span class="hljs-comment line-number">14.</span>+<span class="hljs-comment">--------------------------------------+</span><br><span class="hljs-comment line-number">15.</span>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">16.</span><br><span class="hljs-comment line-number">17.</span>mysql&gt; <span class="hljs-keyword">select</span> <span class="hljs-keyword">datediff</span>(<span class="hljs-string">'1971-01-01'</span>, <span class="hljs-string">'1970-01-05'</span>) / <span class="hljs-number">7</span>;</span>  <span class="hljs-comment">-- 求周数</span><br><span class="hljs-comment line-number">18.</span>+<span class="hljs-comment">------------------------------------------+</span><br><span class="hljs-comment line-number">19.</span>| datediff('1971-01-01', '1970-01-05') / 7 |<br><span class="hljs-comment line-number">20.</span>+<span class="hljs-comment">------------------------------------------+</span><br><span class="hljs-comment line-number">21.</span>|                                  51.5714 |  <span class="hljs-comment">-- 相隔51.5714周</span><br><span class="hljs-comment line-number">22.</span>+<span class="hljs-comment">------------------------------------------+</span><br><span class="hljs-comment line-number">23.</span>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">24.</span><br><span class="hljs-comment line-number">25.</span><span class="hljs-comment">-- FLOOR 和 ROUND函数</span><br><span class="hljs-comment line-number">26.</span><span class="hljs-comment">-- FLOOR(arg)是返回一个不大于arg的最大整数，其实就是取整</span><br><span class="hljs-comment line-number">27.</span>mysql&gt; <span class="hljs-keyword">select</span> <span class="hljs-keyword">floor</span>(<span class="hljs-number">5.4</span>);</span><br><span class="hljs-comment line-number">28.</span>+<span class="hljs-comment">------------+</span><br><span class="hljs-comment line-number">29.</span>| floor(5.4) |<br><span class="hljs-comment line-number">30.</span>+<span class="hljs-comment">------------+</span><br><span class="hljs-comment line-number">31.</span>|          5 |<br><span class="hljs-comment line-number">32.</span>+<span class="hljs-comment">------------+</span><br><span class="hljs-comment line-number">33.</span>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">34.</span><br><span class="hljs-comment line-number">35.</span>mysql&gt; <span class="hljs-keyword">select</span> <span class="hljs-keyword">floor</span>(<span class="hljs-number">5.5</span>);</span><br><span class="hljs-comment line-number">36.</span>+<span class="hljs-comment">------------+</span><br><span class="hljs-comment line-number">37.</span>| floor(5.5) |<br><span class="hljs-comment line-number">38.</span>+<span class="hljs-comment">------------+</span><br><span class="hljs-comment line-number">39.</span>|          5 |<br><span class="hljs-comment line-number">40.</span>+<span class="hljs-comment">------------+</span><br><span class="hljs-comment line-number">41.</span>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">42.</span><br><span class="hljs-comment line-number">43.</span>mysql&gt; <span class="hljs-keyword">select</span> <span class="hljs-keyword">floor</span>(<span class="hljs-number">5.6</span>);</span><br><span class="hljs-comment line-number">44.</span>+<span class="hljs-comment">------------+</span><br><span class="hljs-comment line-number">45.</span>| floor(5.6) |<br><span class="hljs-comment line-number">46.</span>+<span class="hljs-comment">------------+</span><br><span class="hljs-comment line-number">47.</span>|          5 |<br><span class="hljs-comment line-number">48.</span>+<span class="hljs-comment">------------+</span><br><span class="hljs-comment line-number">49.</span>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">50.</span><br><span class="hljs-comment line-number">51.</span><span class="hljs-comment">-- ROUND(X, D) 返回值是对数字X保留到小數点后D位，D默认为0，结果符合四舍五入原则；如果D为负数，则保留小数点左边（整数）的位数</span><br><span class="hljs-comment line-number">52.</span>mysql&gt; <span class="hljs-keyword">select</span> <span class="hljs-keyword">round</span>(<span class="hljs-number">5.4</span>);</span>  <span class="hljs-comment">-- 默认D为0，四舍五入</span><br><span class="hljs-comment line-number">53.</span>+<span class="hljs-comment">------------+</span><br><span class="hljs-comment line-number">54.</span>| round(5.4) |<br><span class="hljs-comment line-number">55.</span>+<span class="hljs-comment">------------+</span><br><span class="hljs-comment line-number">56.</span>|          5 |<br><span class="hljs-comment line-number">57.</span>+<span class="hljs-comment">------------+</span><br><span class="hljs-comment line-number">58.</span>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">59.</span><br><span class="hljs-comment line-number">60.</span>mysql&gt; <span class="hljs-keyword">select</span> <span class="hljs-keyword">round</span>(<span class="hljs-number">5.5</span>);</span>  <span class="hljs-comment">-- 默认D为0，四舍五入</span><br><span class="hljs-comment line-number">61.</span>+<span class="hljs-comment">------------+</span><br><span class="hljs-comment line-number">62.</span>| round(5.5) |<br><span class="hljs-comment line-number">63.</span>+<span class="hljs-comment">------------+</span><br><span class="hljs-comment line-number">64.</span>|          6 |<br><span class="hljs-comment line-number">65.</span>+<span class="hljs-comment">------------+</span><br><span class="hljs-comment line-number">66.</span>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">67.</span><br><span class="hljs-comment line-number">68.</span>mysql&gt; <span class="hljs-keyword">select</span> <span class="hljs-keyword">round</span>(<span class="hljs-number">5.123</span>, <span class="hljs-number">1</span>);</span> <span class="hljs-comment">-- 设D为1，保留小数点右边1为，四舍五入，不进位</span><br><span class="hljs-comment line-number">69.</span>+<span class="hljs-comment">-----------------+</span><br><span class="hljs-comment line-number">70.</span>| round(5.123, 1) |<br><span class="hljs-comment line-number">71.</span>+<span class="hljs-comment">-----------------+</span><br><span class="hljs-comment line-number">72.</span>|             5.1 |<br><span class="hljs-comment line-number">73.</span>+<span class="hljs-comment">-----------------+</span><br><span class="hljs-comment line-number">74.</span>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">75.</span><br><span class="hljs-comment line-number">76.</span>mysql&gt; <span class="hljs-keyword">select</span> <span class="hljs-keyword">round</span>(<span class="hljs-number">5.163</span>, <span class="hljs-number">1</span>);</span> <span class="hljs-comment">-- 设D为1，保留小数点右边1为，四舍五入，进位</span><br><span class="hljs-comment line-number">77.</span>+<span class="hljs-comment">-----------------+</span><br><span class="hljs-comment line-number">78.</span>| round(5.163, 1) |<br><span class="hljs-comment line-number">79.</span>+<span class="hljs-comment">-----------------+</span><br><span class="hljs-comment line-number">80.</span>|             5.2 |<br><span class="hljs-comment line-number">81.</span>+<span class="hljs-comment">-----------------+</span><br><span class="hljs-comment line-number">82.</span>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">83.</span><br><span class="hljs-comment line-number">84.</span>mysql&gt; <span class="hljs-keyword">select</span> <span class="hljs-keyword">round</span>(<span class="hljs-number">524.163</span>, -<span class="hljs-number">1</span>);</span>  <span class="hljs-comment">-- 保留小数点左边1位，不进位</span><br><span class="hljs-comment line-number">85.</span>+<span class="hljs-comment">--------------------+</span><br><span class="hljs-comment line-number">86.</span>| round(524.163, -1) |<br><span class="hljs-comment line-number">87.</span>+<span class="hljs-comment">--------------------+</span><br><span class="hljs-comment line-number">88.</span>|                520 |<br><span class="hljs-comment line-number">89.</span>+<span class="hljs-comment">--------------------+</span><br><span class="hljs-comment line-number">90.</span>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">91.</span><br><span class="hljs-comment line-number">92.</span>mysql&gt; <span class="hljs-keyword">select</span> <span class="hljs-keyword">round</span>(<span class="hljs-number">524.163</span>, -<span class="hljs-number">2</span>);</span>  <span class="hljs-comment">-- 保留小数点左边2位, 不进位</span><br><span class="hljs-comment line-number">93.</span>+<span class="hljs-comment">--------------------+</span><br><span class="hljs-comment line-number">94.</span>| round(524.163, -2) |<br><span class="hljs-comment line-number">95.</span>+<span class="hljs-comment">--------------------+</span><br><span class="hljs-comment line-number">96.</span>|                500 |<br><span class="hljs-comment line-number">97.</span>+<span class="hljs-comment">--------------------+</span><br><span class="hljs-comment line-number">98.</span>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">99.</span><br><span class="hljs-comment line-number">100.</span>mysql&gt; <span class="hljs-keyword">select</span> <span class="hljs-keyword">round</span>(<span class="hljs-number">554.163</span>, -<span class="hljs-number">2</span>);</span>  <span class="hljs-comment">-- 保留小数点左边2位, 进位</span><br><span class="hljs-comment line-number">101.</span>+<span class="hljs-comment">--------------------+</span><br><span class="hljs-comment line-number">102.</span>| round(554.163, -2) |<br><span class="hljs-comment line-number">103.</span>+<span class="hljs-comment">--------------------+</span><br><span class="hljs-comment line-number">104.</span>|                600 |<br><span class="hljs-comment line-number">105.</span>+<span class="hljs-comment">--------------------+</span><br><span class="hljs-comment line-number">106.</span>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">107.</span><br><span class="hljs-comment line-number">108.</span><br><span class="hljs-comment line-number">109.</span><span class="hljs-comment">-- 所以这里使用floor函数取整， 超过一周且不满一周的则落在start 和 end 中间</span><br><span class="hljs-comment line-number">110.</span><br><span class="hljs-comment line-number">111.</span>mysql&gt; <span class="hljs-keyword">select</span> <span class="hljs-keyword">floor</span>(<span class="hljs-keyword">datediff</span>(<span class="hljs-string">'1971-01-01'</span>, <span class="hljs-string">'1970-01-05'</span>) / <span class="hljs-number">7</span>);</span><br><span class="hljs-comment line-number">112.</span>+<span class="hljs-comment">-------------------------------------------------+</span><br><span class="hljs-comment line-number">113.</span>| floor(datediff('1971-01-01', '1970-01-05') / 7) |<br><span class="hljs-comment line-number">114.</span>+<span class="hljs-comment">-------------------------------------------------+</span><br><span class="hljs-comment line-number">115.</span>|                                              51 |  <span class="hljs-comment">-- 取整为51周</span><br><span class="hljs-comment line-number">116.</span>+<span class="hljs-comment">-------------------------------------------------+</span><br><span class="hljs-comment line-number">117.</span>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">118.</span><br><span class="hljs-comment line-number">119.</span>mysql&gt; <span class="hljs-keyword">select</span> <span class="hljs-keyword">floor</span>(<span class="hljs-keyword">datediff</span>(<span class="hljs-string">'1971-01-01'</span>, <span class="hljs-string">'1970-01-05'</span>) / <span class="hljs-number">7</span>) * <span class="hljs-number">7</span>;</span><br><span class="hljs-comment line-number">120.</span>+<span class="hljs-comment">-----------------------------------------------------+</span><br><span class="hljs-comment line-number">121.</span>| floor(datediff('1971-01-01', '1970-01-05') / 7) * 7 |<br><span class="hljs-comment line-number">122.</span>+<span class="hljs-comment">-----------------------------------------------------+</span><br><span class="hljs-comment line-number">123.</span>|                                                 357 |  <span class="hljs-comment">-- 乘以7，得出357天差值</span><br><span class="hljs-comment line-number">124.</span>+<span class="hljs-comment">-----------------------------------------------------+</span><br><span class="hljs-comment line-number">125.</span>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">126.</span><br><span class="hljs-comment line-number">127.</span>mysql&gt; <span class="hljs-keyword">select</span> adddate(<span class="hljs-string">'1970-01-05'</span>, <span class="hljs-built_in">interval</span> <span class="hljs-keyword">floor</span>(<span class="hljs-keyword">datediff</span>(<span class="hljs-string">'1971-01-01'</span>, <span class="hljs-string">'1970-01-05'</span>)/<span class="hljs-number">7</span>)*<span class="hljs-number">7</span> <span class="hljs-keyword">day</span>) <span class="hljs-keyword">as</span> Monday_start;</span>    <br><span class="hljs-comment line-number">128.</span><span class="hljs-comment">-- 使用adddate函数，在1970-01-05(周一)的基础上，增加357天（51周），得到的值1970-12-28也是周一，标记为start</span><br><span class="hljs-comment line-number">129.</span>+<span class="hljs-comment">--------------+</span><br><span class="hljs-comment line-number">130.</span>| Monday_start |<br><span class="hljs-comment line-number">131.</span>+<span class="hljs-comment">--------------+</span><br><span class="hljs-comment line-number">132.</span>| 1970-12-28   |  <span class="hljs-comment">-- 1970-12-28（周一），而传入的值1971-01-01是该周的周五</span><br><span class="hljs-comment line-number">133.</span>+<span class="hljs-comment">--------------+</span><br><span class="hljs-comment line-number">134.</span>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">135.</span><br><span class="hljs-comment line-number">136.</span><span class="hljs-comment">-- 以同样的方法得到周日</span><br><span class="hljs-comment line-number">137.</span>mysql&gt; <span class="hljs-keyword">select</span> adddate(<span class="hljs-string">'1970-01-05'</span>, <span class="hljs-built_in">interval</span> <span class="hljs-keyword">floor</span>(<span class="hljs-keyword">datediff</span>(<span class="hljs-string">'1971-01-01'</span>, <span class="hljs-string">'1970-01-05'</span>)/<span class="hljs-number">7</span>)*<span class="hljs-number">7</span> + <span class="hljs-number">6</span> <span class="hljs-keyword">day</span>) <span class="hljs-keyword">as</span> Sunday_end;</span><br><span class="hljs-comment line-number">138.</span>+<span class="hljs-comment">------------+</span><br><span class="hljs-comment line-number">139.</span>| Sunday_end |<br><span class="hljs-comment line-number">140.</span>+<span class="hljs-comment">------------+</span><br><span class="hljs-comment line-number">141.</span>| 1971-01-03 |<br><span class="hljs-comment line-number">142.</span>+<span class="hljs-comment">------------+</span><br><span class="hljs-comment line-number">143.</span>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">144.</span><br><span class="hljs-comment line-number">145.</span><span class="hljs-comment">-- 通过以上的周一和周日的计算，即可求出1971-01-01这天所在的一周，得出start（周一）和end（周日）</span><br><span class="hljs-comment line-number">146.</span><span class="hljs-comment">-- 然后对start和end以及o_custkey做分组操作，并通过count(o_orderkey)得到分组的定单量</span></span><br></code></pre></li></ol></li>
<li><p><strong>使用子查询实现RowNumber</strong></p>

<ol>
<li rel="1"><p>思路</p>

<ul>
<li>假设当前在第N行记录，通过主键emp_no遍历有多少行的记录<code>小于等于</code>当前行,即为当前行的行数</li></ul></li>
<li rel="2"><p>SQL语句</p>

<pre class="prettyprint hljs-dark"><code class="language-sql hljs"><span class="hljs-comment line-number">1.</span><span class="hljs-operator"><span class="hljs-keyword">SELECT</span> <br><span class="hljs-comment line-number">2.</span>(<span class="hljs-keyword">SELECT</span> <span class="hljs-keyword">COUNT</span>(<span class="hljs-number">1</span>) <span class="hljs-keyword">FROM</span> employees b <span class="hljs-keyword">WHERE</span> b.emp_no &lt;= a.emp_no ) <span class="hljs-keyword">AS</span> row_number,<br><span class="hljs-comment line-number">3.</span>emp_no,<span class="hljs-keyword">CONCAT</span>(last_name,<span class="hljs-string">" "</span>,first_name) <span class="hljs-keyword">name</span>,gender,hire_date<br><span class="hljs-comment line-number">4.</span><span class="hljs-keyword">FROM</span> employees a <span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span> emp_no <span class="hljs-keyword">LIMIT</span> <span class="hljs-number">10</span>;</span><br><span class="hljs-comment line-number">5.</span><br><span class="hljs-comment line-number">6.</span><span class="hljs-comment">-- 假设当前在第5行</span><br><span class="hljs-comment line-number">7.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span>  b.emp_no  <span class="hljs-keyword">from</span> employees.employees <span class="hljs-keyword">as</span> b <span class="hljs-keyword">order</span> <span class="hljs-keyword">by</span> b.emp_no <span class="hljs-keyword">limit</span> <span class="hljs-number">5</span>;</span><br><span class="hljs-comment line-number">8.</span>+<span class="hljs-comment">--------+</span><br><span class="hljs-comment line-number">9.</span>| emp_no |<br><span class="hljs-comment line-number">10.</span>+<span class="hljs-comment">--------+</span><br><span class="hljs-comment line-number">11.</span>|  10001 |<br><span class="hljs-comment line-number">12.</span>|  10002 |<br><span class="hljs-comment line-number">13.</span>|  10003 |<br><span class="hljs-comment line-number">14.</span>|  10004 |<br><span class="hljs-comment line-number">15.</span>|  10005 |  <span class="hljs-comment">-- 第5行的emp_no是10005</span><br><span class="hljs-comment line-number">16.</span>+<span class="hljs-comment">--------+</span><br><span class="hljs-comment line-number">17.</span>5 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">18.</span><br><span class="hljs-comment line-number">19.</span>mysql&gt; <span class="hljs-keyword">select</span>  <span class="hljs-keyword">count</span>(*)  <span class="hljs-keyword">from</span> employees.employees <span class="hljs-keyword">as</span> b <span class="hljs-keyword">where</span> b.emp_no &lt;= <span class="hljs-number">10005</span> <span class="hljs-keyword">order</span> <span class="hljs-keyword">by</span> b.emp_no;</span><br><span class="hljs-comment line-number">20.</span>查找小于等于5的行数有几行<br><span class="hljs-comment line-number">21.</span>+<span class="hljs-comment">----------+</span><br><span class="hljs-comment line-number">22.</span>| count(*) |<br><span class="hljs-comment line-number">23.</span>+<span class="hljs-comment">----------+</span><br><span class="hljs-comment line-number">24.</span>|        5 |  <span class="hljs-comment">-- 小于等于10005的记录有5行，则5就是10005该行记录的行号</span><br><span class="hljs-comment line-number">25.</span>+<span class="hljs-comment">----------+</span><br><span class="hljs-comment line-number">26.</span>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">27.</span><br><span class="hljs-comment line-number">28.</span><span class="hljs-comment">-- 将该子查询的结果即可作为RowNumber</span><br><span class="hljs-comment line-number">29.</span><span class="hljs-comment">-- 但是该子查询的效率较低。不推荐使用。</span><br><span class="hljs-comment line-number">30.</span><br><span class="hljs-comment line-number">31.</span><span class="hljs-comment">-- 推荐Day012中提及的自定义变量方式</span><br><span class="hljs-comment line-number">32.</span><span class="hljs-keyword">SELECT</span> @a:=@a+<span class="hljs-number">1</span> <span class="hljs-keyword">AS</span> row_number,<br><span class="hljs-comment line-number">33.</span>    emp_no,<span class="hljs-keyword">CONCAT</span>(last_name,<span class="hljs-string">" "</span>,first_name) <span class="hljs-keyword">name</span>,gender,hire_date<br><span class="hljs-comment line-number">34.</span>    <span class="hljs-keyword">FROM</span> employees,(<span class="hljs-keyword">SELECT</span> @a:=<span class="hljs-number">0</span>) <span class="hljs-keyword">AS</span> a <span class="hljs-keyword">LIMIT</span> <span class="hljs-number">10</span>;</span><br></code></pre></li></ol></li>
</ul>

<hr></div><div id="wmd-preview-section-554" class="wmd-preview-section preview-content">

<h2 id="二-rank">二. Rank</h2>

<blockquote>
  <p>给出不同的用户的分数，然后根据分数计算排名</p>
</blockquote></div><div id="wmd-preview-section-5" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs"><span class="hljs-comment line-number">1.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> test_rank(<span class="hljs-keyword">id</span> <span class="hljs-built_in">int</span>, score <span class="hljs-built_in">int</span>);</span><br><span class="hljs-comment line-number">2.</span>Query OK, 0 rows affected (0.16 sec)<br><span class="hljs-comment line-number">3.</span><br><span class="hljs-comment line-number">4.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> test_rank <span class="hljs-keyword">values</span>(<span class="hljs-number">1</span>, <span class="hljs-number">10</span>), (<span class="hljs-number">2</span>, <span class="hljs-number">20</span>), (<span class="hljs-number">3</span>, <span class="hljs-number">30</span>), (<span class="hljs-number">4</span>, <span class="hljs-number">30</span>), (<span class="hljs-number">5</span>, <span class="hljs-number">40</span>), (<span class="hljs-number">6</span>, <span class="hljs-number">40</span>);</span><br><span class="hljs-comment line-number">5.</span>Query OK, 6 rows affected (0.05 sec)<br><span class="hljs-comment line-number">6.</span>Records: 6  Duplicates: 0  Warnings: 0<br><span class="hljs-comment line-number">7.</span><br><span class="hljs-comment line-number">8.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> test_rank;</span><br><span class="hljs-comment line-number">9.</span>+<span class="hljs-comment">------+-------+</span><br><span class="hljs-comment line-number">10.</span>| id   | score |<br><span class="hljs-comment line-number">11.</span>+<span class="hljs-comment">------+-------+</span><br><span class="hljs-comment line-number">12.</span>|    1 |    10 |<br><span class="hljs-comment line-number">13.</span>|    2 |    20 |<br><span class="hljs-comment line-number">14.</span>|    3 |    30 |<br><span class="hljs-comment line-number">15.</span>|    4 |    30 |<br><span class="hljs-comment line-number">16.</span>|    5 |    40 |<br><span class="hljs-comment line-number">17.</span>|    6 |    40 |<br><span class="hljs-comment line-number">18.</span>+<span class="hljs-comment">------+-------+</span><br><span class="hljs-comment line-number">19.</span>6 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">20.</span><br><span class="hljs-comment line-number">21.</span>mysql&gt; <span class="hljs-keyword">set</span> @prev_value := <span class="hljs-literal">NULL</span>;</span>  <span class="hljs-comment">-- 假设比较到第N行，设置一个变量prev_value用于存放第N-1行score的分数</span><br><span class="hljs-comment line-number">22.</span>                                 <span class="hljs-comment">-- 用于比较第N行的score和第N-1行的score</span><br><span class="hljs-comment line-number">23.</span>                                 <span class="hljs-comment">-- prev_value可以理解为 是临时保存第N-1行的score的变量</span><br><span class="hljs-comment line-number">24.</span>Query OK, 0 rows affected (0.00 sec)<br><span class="hljs-comment line-number">25.</span><br><span class="hljs-comment line-number">26.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">set</span> @rank_count := <span class="hljs-number">0</span>;</span>     <span class="hljs-comment">-- 用于存放当前的排名</span><br><span class="hljs-comment line-number">27.</span>Query OK, 0 rows affected (0.00 sec)<br><span class="hljs-comment line-number">28.</span><br><span class="hljs-comment line-number">29.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span>  <span class="hljs-keyword">id</span>, score, <br><span class="hljs-comment line-number">30.</span>    -&gt; <span class="hljs-keyword">case</span><br><span class="hljs-comment line-number">31.</span>    -&gt;     <span class="hljs-keyword">when</span> @prev_value = score <span class="hljs-keyword">then</span> @rank_count  <br><span class="hljs-comment line-number">32.</span>           <span class="hljs-comment">-- 相等则prev_value不变， 并返回rank_count（第一次为NULL，不会相等，所以跳转到下一个when语句）</span><br><span class="hljs-comment line-number">33.</span>    -&gt;     <span class="hljs-keyword">when</span> @prev_value := score <span class="hljs-keyword">then</span> @rank_count := @rank_count + <span class="hljs-number">1</span> <br><span class="hljs-comment line-number">34.</span>           <span class="hljs-comment">-- 不等，则第N行的score赋值(:=)给prev_value。且rank_count增加1</span><br><span class="hljs-comment line-number">35.</span>    -&gt; <span class="hljs-keyword">end</span> <span class="hljs-keyword">as</span> rank_column   <span class="hljs-comment">-- case 开始的，end结尾</span><br><span class="hljs-comment line-number">36.</span>    -&gt; <span class="hljs-keyword">from</span> test_rank<br><span class="hljs-comment line-number">37.</span>    -&gt; <span class="hljs-keyword">order</span> <span class="hljs-keyword">by</span> score <span class="hljs-keyword">desc</span>;</span><br><span class="hljs-comment line-number">38.</span>+<span class="hljs-comment">------+-------+-------------+</span><br><span class="hljs-comment line-number">39.</span>| id   | score | rank_column |<br><span class="hljs-comment line-number">40.</span>+<span class="hljs-comment">------+-------+-------------+</span><br><span class="hljs-comment line-number">41.</span>|    5 |    40 |           1 |<br><span class="hljs-comment line-number">42.</span>|    6 |    40 |           1 |<br><span class="hljs-comment line-number">43.</span>|    3 |    30 |           2 |<br><span class="hljs-comment line-number">44.</span>|    4 |    30 |           2 |<br><span class="hljs-comment line-number">45.</span>|    2 |    20 |           3 |<br><span class="hljs-comment line-number">46.</span>|    1 |    10 |           4 |<br><span class="hljs-comment line-number">47.</span>+<span class="hljs-comment">------+-------+-------------+</span><br><span class="hljs-comment line-number">48.</span>6 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">49.</span><br><span class="hljs-comment line-number">50.</span><span class="hljs-comment">-- case  </span><br><span class="hljs-comment line-number">51.</span><span class="hljs-comment">--   when [condition_1] then [do_something_1] </span><br><span class="hljs-comment line-number">52.</span><span class="hljs-comment">--   when [condition_2] then [do_something_2] </span><br><span class="hljs-comment line-number">53.</span><span class="hljs-comment">--   end</span><br><span class="hljs-comment line-number">54.</span><span class="hljs-comment">-- 语法：如果 condition_1条件满足，则执行 do_something_1 然后就跳出,不会执行condition_2;</span><br><span class="hljs-comment line-number">55.</span><span class="hljs-comment">--       如果 condition_1条件不满足，则继续执行到 condition_2。以此类推。</span><br><span class="hljs-comment line-number">56.</span><br><span class="hljs-comment line-number">57.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">58.</span><span class="hljs-comment">-- 作业：使用一条语句写出Rank</span><br><span class="hljs-comment line-number">59.</span><span class="hljs-comment">--</span></span><br></code></pre>

<hr>

</div><div id="wmd-preview-section-4872" class="wmd-preview-section preview-content">

<h2 id="三-视图">三. 视图</h2>

<blockquote>
  <p><a href="http://dev.mysql.com/doc/refman/5.7/en/create-view.html" target="_blank">官方文档</a></p>
</blockquote>

<ul><li><strong>创建视图</strong></li>
</ul></div><div id="wmd-preview-section-82" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs"><span class="hljs-comment line-number">1.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">2.</span><span class="hljs-comment">-- 创建视图</span><br><span class="hljs-comment line-number">3.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">4.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">create</span> <span class="hljs-keyword">view</span> view_rank <span class="hljs-keyword">as</span> <span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> test_rank;</span>  <span class="hljs-comment">-- 针对上面的test_rank创建一个视图</span><br><span class="hljs-comment line-number">5.</span>Query OK, 0 rows affected (0.03 sec)<br><span class="hljs-comment line-number">6.</span><br><span class="hljs-comment line-number">7.</span><span class="hljs-comment">-- 也可以对select结果增加条件进行过滤后，再创建视图</span><br><span class="hljs-comment line-number">8.</span><br><span class="hljs-comment line-number">9.</span><br><span class="hljs-comment line-number">10.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">show</span> <span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> test_rank\<span class="hljs-keyword">G</span><br><span class="hljs-comment line-number">11.</span>*************************** <span class="hljs-number">1.</span> <span class="hljs-keyword">row</span> ***************************<br><span class="hljs-comment line-number">12.</span>       <span class="hljs-keyword">Table</span>: test_rank<br><span class="hljs-comment line-number">13.</span><span class="hljs-keyword">Create</span> <span class="hljs-keyword">Table</span>: <span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> <span class="hljs-string">`test_rank`</span> (    <span class="hljs-comment">-- 得到的是表结构</span><br><span class="hljs-comment line-number">14.</span>  <span class="hljs-string">`id`</span> <span class="hljs-built_in">int</span>(<span class="hljs-number">11</span>) <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span>,<br><span class="hljs-comment line-number">15.</span>  <span class="hljs-string">`score`</span> <span class="hljs-built_in">int</span>(<span class="hljs-number">11</span>) <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">16.</span>) <span class="hljs-keyword">ENGINE</span>=<span class="hljs-keyword">InnoDB</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-keyword">CHARSET</span>=utf8mb4<br><span class="hljs-comment line-number">17.</span><span class="hljs-number">1</span> <span class="hljs-keyword">row</span> <span class="hljs-keyword">in</span> <span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">18.</span><br><span class="hljs-comment line-number">19.</span><br><span class="hljs-comment line-number">20.</span>mysql&gt; <span class="hljs-keyword">show</span> <span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> view_rank\<span class="hljs-keyword">G</span>  <span class="hljs-comment">-- 他是以一张表的形式存在的，可通过show tables看到</span><br><span class="hljs-comment line-number">21.</span>*************************** <span class="hljs-number">1.</span> <span class="hljs-keyword">row</span> ***************************<br><span class="hljs-comment line-number">22.</span>                <span class="hljs-keyword">View</span>: view_rank<br><span class="hljs-comment line-number">23.</span>         <span class="hljs-keyword">Create</span> <span class="hljs-keyword">View</span>: <span class="hljs-keyword">CREATE</span> ALGORITHM=UNDEFINED DEFINER=<span class="hljs-string">`root`</span>@<span class="hljs-string">`localhost`</span> <span class="hljs-keyword">SQL</span> <span class="hljs-keyword">SECURITY</span> DEFINER <span class="hljs-keyword">VIEW</span> <span class="hljs-string">`view_rank`</span> <span class="hljs-keyword">AS</span> <span class="hljs-keyword">select</span> <span class="hljs-string">`test_rank`</span>.<span class="hljs-string">`id`</span> <span class="hljs-keyword">AS</span> <span class="hljs-string">`id`</span>,<span class="hljs-string">`test_rank`</span>.<span class="hljs-string">`score`</span> <span class="hljs-keyword">AS</span> <span class="hljs-string">`score`</span> <span class="hljs-keyword">from</span> <span class="hljs-string">`test_rank`</span><br><span class="hljs-comment line-number">24.</span>                      <span class="hljs-comment">-- 和真正的表不同的是，这里show出来的是视图的定义</span><br><span class="hljs-comment line-number">25.</span>character_set_client: utf8<br><span class="hljs-comment line-number">26.</span>collation_connection: utf8_general_ci<br><span class="hljs-comment line-number">27.</span><span class="hljs-number">1</span> <span class="hljs-keyword">row</span> <span class="hljs-keyword">in</span> <span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">28.</span><br><span class="hljs-comment line-number">29.</span>mysql&gt; <span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> view_rank;</span>  <span class="hljs-comment">-- 可以直接查询该视图得结果</span><br><span class="hljs-comment line-number">30.</span>+<span class="hljs-comment">------+-------+</span><br><span class="hljs-comment line-number">31.</span>| id   | score |<br><span class="hljs-comment line-number">32.</span>+<span class="hljs-comment">------+-------+</span><br><span class="hljs-comment line-number">33.</span>|    1 |    10 |<br><span class="hljs-comment line-number">34.</span>|    2 |    20 |<br><span class="hljs-comment line-number">35.</span>|    3 |    30 |<br><span class="hljs-comment line-number">36.</span>|    4 |    30 |<br><span class="hljs-comment line-number">37.</span>|    5 |    40 |<br><span class="hljs-comment line-number">38.</span>|    6 |    40 |<br><span class="hljs-comment line-number">39.</span>+<span class="hljs-comment">------+-------+</span><br><span class="hljs-comment line-number">40.</span>6 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">41.</span><br><span class="hljs-comment line-number">42.</span><span class="hljs-comment">-- 视图的作用是，可以对开发人员是透明的，可以隐藏部分关键的列</span><br><span class="hljs-comment line-number">43.</span><span class="hljs-comment">-- 视图在mysql是虚拟表。根据视图的定义，还是取执行定义中的select语句。</span><br><span class="hljs-comment line-number">44.</span><br><span class="hljs-comment line-number">45.</span><br><span class="hljs-comment line-number">46.</span><span class="hljs-comment">-- 只开放部分列</span><br><span class="hljs-comment line-number">47.</span>mysql&gt; <span class="hljs-keyword">create</span> <span class="hljs-keyword">view</span> view_rank_1 <span class="hljs-keyword">as</span> <span class="hljs-keyword">select</span> <span class="hljs-keyword">id</span> <span class="hljs-keyword">from</span> test_rank;</span> <span class="hljs-comment">-- 只开放id列</span><br><span class="hljs-comment line-number">48.</span>Query OK, 0 rows affected (0.04 sec)<br><span class="hljs-comment line-number">49.</span><br><span class="hljs-comment line-number">50.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> view_rank_1;</span>  <span class="hljs-comment">-- 即使 select * ，也只能看到id列，具有隐藏原来表中部分列的功能                        </span><br><span class="hljs-comment line-number">51.</span>+<span class="hljs-comment">------+</span><br><span class="hljs-comment line-number">52.</span>| id   |<br><span class="hljs-comment line-number">53.</span>+<span class="hljs-comment">------+</span><br><span class="hljs-comment line-number">54.</span>|    1 |<br><span class="hljs-comment line-number">55.</span>|    2 |<br><span class="hljs-comment line-number">56.</span>|    3 |<br><span class="hljs-comment line-number">57.</span>|    4 |<br><span class="hljs-comment line-number">58.</span>|    5 |<br><span class="hljs-comment line-number">59.</span>|    6 |<br><span class="hljs-comment line-number">60.</span>+<span class="hljs-comment">------+</span><br><span class="hljs-comment line-number">61.</span>6 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">62.</span><br><span class="hljs-comment line-number">63.</span><span class="hljs-comment">-- 不要取用select * from 去创建视图，因为mysql会把*逐个解析成列。</span><br><span class="hljs-comment line-number">64.</span><span class="hljs-comment">-- 当原来的表结构发生变化时，视图的表结构是不会发生变化的，视图在创建的瞬间，便确定了结构。</span><br><span class="hljs-comment line-number">65.</span><span class="hljs-comment">-- 比如，当你alter原来的表 增加列(add columns)时,再去查询该视图,新增加的列是不存在的。</span><br><span class="hljs-comment line-number">66.</span><br><span class="hljs-comment line-number">67.</span>mysql&gt; <span class="hljs-keyword">alter</span> <span class="hljs-keyword">table</span> test_rank <span class="hljs-keyword">add</span> <span class="hljs-keyword">column</span>  <span class="hljs-keyword">c</span> <span class="hljs-built_in">int</span> <span class="hljs-keyword">default</span> <span class="hljs-number">0</span>;</span> <span class="hljs-comment">-- 增加一列名字为c，默认值为0</span><br><span class="hljs-comment line-number">68.</span>Query OK, 0 rows affected (0.30 sec)<br><span class="hljs-comment line-number">69.</span>Records: 0  Duplicates: 0  Warnings: 0<br><span class="hljs-comment line-number">70.</span><br><span class="hljs-comment line-number">71.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> test_rank;</span>  <span class="hljs-comment">-- 查询原表，新的列c出现了</span><br><span class="hljs-comment line-number">72.</span>+<span class="hljs-comment">------+-------+------+</span><br><span class="hljs-comment line-number">73.</span>| id   | score | c    |<br><span class="hljs-comment line-number">74.</span>+<span class="hljs-comment">------+-------+------+</span><br><span class="hljs-comment line-number">75.</span>|    1 |    10 |    0 |<br><span class="hljs-comment line-number">76.</span>|    2 |    20 |    0 |<br><span class="hljs-comment line-number">77.</span>|    3 |    30 |    0 |<br><span class="hljs-comment line-number">78.</span>|    4 |    30 |    0 |<br><span class="hljs-comment line-number">79.</span>|    5 |    40 |    0 |<br><span class="hljs-comment line-number">80.</span>|    6 |    40 |    0 |<br><span class="hljs-comment line-number">81.</span>+<span class="hljs-comment">------+-------+------+</span><br><span class="hljs-comment line-number">82.</span>6 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">83.</span><br><span class="hljs-comment line-number">84.</span>mysql&gt; <span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> view_rank;</span> <span class="hljs-comment">-- 尽管view_rank用select * 创建，但当时没有列c，所以无法得到c列的值</span><br><span class="hljs-comment line-number">85.</span>+<span class="hljs-comment">------+-------+</span><br><span class="hljs-comment line-number">86.</span>| id   | score |<br><span class="hljs-comment line-number">87.</span>+<span class="hljs-comment">------+-------+</span><br><span class="hljs-comment line-number">88.</span>|    1 |    10 |<br><span class="hljs-comment line-number">89.</span>|    2 |    20 |<br><span class="hljs-comment line-number">90.</span>|    3 |    30 |<br><span class="hljs-comment line-number">91.</span>|    4 |    30 |<br><span class="hljs-comment line-number">92.</span>|    5 |    40 |<br><span class="hljs-comment line-number">93.</span>|    6 |    40 |<br><span class="hljs-comment line-number">94.</span>+<span class="hljs-comment">------+-------+</span><br><span class="hljs-comment line-number">95.</span>6 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">96.</span><br><span class="hljs-comment line-number">97.</span><span class="hljs-comment">-- 注意：mysql中的视图都是虚拟表。不像Oracle可以物化成真实存在的表。</span><br><span class="hljs-comment line-number">98.</span><span class="hljs-comment">--      每次查询视图，实际上还是去查询的原来的表，只是查询的规则是在视图创建时经过定义的。</span><br><span class="hljs-comment line-number">99.</span></span><br></code></pre>

<ul><li><p><strong>视图的算法</strong> <br>
视图的算法(<code>ALGORITHM</code>)有三种方式：</p>

<ul>
<li><code>UNDEFINED</code> 默认方式，由MySQL来判断使用下面的哪种算法</li>
<li><code>MERGE</code> ： <code>每次</code>通过<code>物理表</code>查询得到结果，把结果merge(合并)起来返回</li>
<li><code>TEMPTABLE</code> ： 产生一张<code>临时表</code>，把数据放入临时表后，客户端再去临时表取数据（<code>不会缓存</code>）</li></ul>

<blockquote>
  <p><code>TEMPTABLE 特点</code>：即使访问条件一样，第二次查询还是会去读取物理表中的内容，并重新生成一张临时表,并不会取缓存之前的表。<em>（临时表是Memory存储引擎，默认放内存，超过配置大小放磁盘）</em></p>
  
  <p>当查询有一个较大的结果集时，使用<code>TEMPTABLE</code>可以快速的结束对该物理表的访问，从而可以快速释放这张物理表上占用的资源。然后客户端可以对临时表上的数据做一些耗时的操作，而不影响原来的物理表。</p>
</blockquote>

<p><strong>所以一般我们使用<code>UNDEFINED</code>，由MySQL自己去判断</strong></p></li>
</ul>

<hr></div><div id="wmd-preview-section-1158" class="wmd-preview-section preview-content">

<h2 id="四-union">四. UNION</h2>

<ol><li rel="1"><code>UNION</code> 的作用是将两个查询的结果集进行合并。</li>
<li rel="2">UNION必须由<code>两条或两条以上</code>的SELECT语句组成，语句之间用关键字<code>UNION</code>分隔。</li>
<li rel="3">UNION中的每个查询必须包含相同的列（<code>类型相同或可以隐式转换</code>）、表达式或聚集函数。</li>
</ol></div><div id="wmd-preview-section-3553" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs"><span class="hljs-comment line-number">1.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> test_union_1(a <span class="hljs-built_in">int</span>, b <span class="hljs-built_in">int</span>);</span><br><span class="hljs-comment line-number">2.</span>Query OK, 0 rows affected (0.18 sec)<br><span class="hljs-comment line-number">3.</span><br><span class="hljs-comment line-number">4.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> test_union_2(a <span class="hljs-built_in">int</span>, <span class="hljs-keyword">c</span> <span class="hljs-built_in">int</span>);</span>  <br><span class="hljs-comment line-number">5.</span>Query OK, 0 rows affected (0.15 sec)<br><span class="hljs-comment line-number">6.</span><br><span class="hljs-comment line-number">7.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> test_union_1 <span class="hljs-keyword">values</span>(<span class="hljs-number">1</span>, <span class="hljs-number">2</span>), (<span class="hljs-number">3</span>, <span class="hljs-number">4</span>), (<span class="hljs-number">5</span>, <span class="hljs-number">6</span>), (<span class="hljs-number">10</span>, <span class="hljs-number">20</span>);</span><br><span class="hljs-comment line-number">8.</span>Query OK, 4 rows affected (0.06 sec)<br><span class="hljs-comment line-number">9.</span>Records: 4  Duplicates: 0  Warnings: 0<br><span class="hljs-comment line-number">10.</span><br><span class="hljs-comment line-number">11.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> test_union_2 <span class="hljs-keyword">values</span>(<span class="hljs-number">10</span>, <span class="hljs-number">20</span>), (<span class="hljs-number">30</span>, <span class="hljs-number">40</span>), (<span class="hljs-number">50</span>, <span class="hljs-number">60</span>);</span>  <br><span class="hljs-comment line-number">12.</span>Query OK, 3 rows affected (0.03 sec)<br><span class="hljs-comment line-number">13.</span>Records: 3  Duplicates: 0  Warnings: 0<br><span class="hljs-comment line-number">14.</span><br><span class="hljs-comment line-number">15.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> test_union_1;</span><br><span class="hljs-comment line-number">16.</span>+<span class="hljs-comment">------+------+</span><br><span class="hljs-comment line-number">17.</span>| a    | b    |<br><span class="hljs-comment line-number">18.</span>+<span class="hljs-comment">------+------+</span><br><span class="hljs-comment line-number">19.</span>|    1 |    2 |<br><span class="hljs-comment line-number">20.</span>|    3 |    4 |<br><span class="hljs-comment line-number">21.</span>|    5 |    6 |<br><span class="hljs-comment line-number">22.</span>|   10 |   20 |  <span class="hljs-comment">-- test_union_1 中的10, 20</span><br><span class="hljs-comment line-number">23.</span>+<span class="hljs-comment">------+------+</span><br><span class="hljs-comment line-number">24.</span>4 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">25.</span><br><span class="hljs-comment line-number">26.</span>mysql&gt; <span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> test_union_2;</span><br><span class="hljs-comment line-number">27.</span>+<span class="hljs-comment">------+------+</span><br><span class="hljs-comment line-number">28.</span>| a    | c    |<br><span class="hljs-comment line-number">29.</span>+<span class="hljs-comment">------+------+</span><br><span class="hljs-comment line-number">30.</span>|   10 |   20 | <span class="hljs-comment">-- test_union_2 中的10, 20</span><br><span class="hljs-comment line-number">31.</span>|   30 |   40 |<br><span class="hljs-comment line-number">32.</span>|   50 |   60 |  <br><span class="hljs-comment line-number">33.</span>+<span class="hljs-comment">------+------+</span><br><span class="hljs-comment line-number">34.</span>3 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">35.</span><br><span class="hljs-comment line-number">36.</span>mysql&gt; <span class="hljs-keyword">select</span> a, b <span class="hljs-keyword">as</span> <span class="hljs-keyword">t</span> <span class="hljs-keyword">from</span> test_union_1<br><span class="hljs-comment line-number">37.</span>    -&gt; <span class="hljs-keyword">union</span><br><span class="hljs-comment line-number">38.</span>    -&gt; <span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> test_union_2;</span><br><span class="hljs-comment line-number">39.</span>+<span class="hljs-comment">------+------+</span><br><span class="hljs-comment line-number">40.</span>| a    | t    |<br><span class="hljs-comment line-number">41.</span>+<span class="hljs-comment">------+------+</span><br><span class="hljs-comment line-number">42.</span>|    1 |    2 |<br><span class="hljs-comment line-number">43.</span>|    3 |    4 |<br><span class="hljs-comment line-number">44.</span>|    5 |    6 |<br><span class="hljs-comment line-number">45.</span>|   10 |   20 | <span class="hljs-comment">-- 只出现了一次 10, 20，union会去重</span><br><span class="hljs-comment line-number">46.</span>|   30 |   40 |<br><span class="hljs-comment line-number">47.</span>|   50 |   60 |<br><span class="hljs-comment line-number">48.</span>+<span class="hljs-comment">------+------+</span><br><span class="hljs-comment line-number">49.</span>6 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">50.</span><br><span class="hljs-comment line-number">51.</span>mysql&gt; <span class="hljs-keyword">select</span> a, b <span class="hljs-keyword">as</span> <span class="hljs-keyword">t</span> <span class="hljs-keyword">from</span> test_union_1<br><span class="hljs-comment line-number">52.</span>    -&gt; <span class="hljs-keyword">union</span> all   <span class="hljs-comment">-- 使用 union all 可以不去重</span><br><span class="hljs-comment line-number">53.</span>    -&gt; <span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> test_union_2;</span><br><span class="hljs-comment line-number">54.</span>+<span class="hljs-comment">------+------+</span><br><span class="hljs-comment line-number">55.</span>| a    | t    |<br><span class="hljs-comment line-number">56.</span>+<span class="hljs-comment">------+------+</span><br><span class="hljs-comment line-number">57.</span>|    1 |    2 |<br><span class="hljs-comment line-number">58.</span>|    3 |    4 |<br><span class="hljs-comment line-number">59.</span>|    5 |    6 |<br><span class="hljs-comment line-number">60.</span>|   10 |   20 | <span class="hljs-comment">-- test_union_1 中的10, 20</span><br><span class="hljs-comment line-number">61.</span>|   10 |   20 | <span class="hljs-comment">-- test_union_2 中的10, 20</span><br><span class="hljs-comment line-number">62.</span>|   30 |   40 |<br><span class="hljs-comment line-number">63.</span>|   50 |   60 |<br><span class="hljs-comment line-number">64.</span>+<span class="hljs-comment">------+------+</span><br><span class="hljs-comment line-number">65.</span>7 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">66.</span><br><span class="hljs-comment line-number">67.</span>mysql&gt; <span class="hljs-keyword">select</span> a, b <span class="hljs-keyword">as</span> <span class="hljs-keyword">t</span> <span class="hljs-keyword">from</span> test_union_1 <span class="hljs-keyword">where</span> a &gt; <span class="hljs-number">2</span> <br><span class="hljs-comment line-number">68.</span>    -&gt; <span class="hljs-keyword">union</span><br><span class="hljs-comment line-number">69.</span>    -&gt; <span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> test_union_2 <span class="hljs-keyword">where</span> <span class="hljs-keyword">c</span> &gt; <span class="hljs-number">50</span>;</span>  <span class="hljs-comment">-- 使用where过滤也可以 </span><br><span class="hljs-comment line-number">70.</span>+<span class="hljs-comment">------+------+</span><br><span class="hljs-comment line-number">71.</span>| a    | t    |<br><span class="hljs-comment line-number">72.</span>+<span class="hljs-comment">------+------+</span><br><span class="hljs-comment line-number">73.</span>|    3 |    4 |<br><span class="hljs-comment line-number">74.</span>|    5 |    6 |<br><span class="hljs-comment line-number">75.</span>|   10 |   20 |<br><span class="hljs-comment line-number">76.</span>|   50 |   60 |<br><span class="hljs-comment line-number">77.</span>+<span class="hljs-comment">------+------+</span><br><span class="hljs-comment line-number">78.</span>4 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)</span><br></code></pre>

<blockquote>
  <p>如果知道数据本身具有唯一性，没有重复，则建议使用<code>union all</code>，因为<code>union</code>会做<code>去重操作</code>，性能会比<code>union all</code>要低</p>
</blockquote>

<hr></div><div id="wmd-preview-section-7046" class="wmd-preview-section preview-content">

<h2 id="五-触发器">五. 触发器</h2>

<blockquote>
  <p><a href="http://dev.mysql.com/doc/refman/5.7/en/create-trigger.html" target="_blank">官方文档</a></p>
</blockquote>

<ul><li><p><strong>定义</strong></p>

<ul>
<li>触发器的对象是<code>表</code>，当表上出现<code>特定的事件</code>时<code>触发</code>该程序的执行</li></ul></li>
<li><p><strong>触发器的类型</strong></p>

<ul>
<li><p><strong><code>UPDATE</code></strong></p>

<ul>
<li>update 操作</li></ul></li>
<li><p><strong><code>DELETE</code></strong></p>

<ul>
<li>delete 操作</li>
<li>replace 操作 <br>
<ul>
<li><strong>注意：</strong>drop，truncate等DDL操作<code>不会触发</code>DELETE</li></ul></li></ul></li>
<li><strong><code>INSERT</code></strong> <br>
<ul>
<li>insert 操作</li>
<li>load data 操作</li>
<li>replace 操作</li></ul></li></ul>

<blockquote>
  <p>注意，<code>replace</code>操作会<strong><code>触发两次</code></strong>，一次是<code>UPDATE</code>类型的触发器，一次是<code>INSERT</code>类型的触发器 <br>
  MySQL 5.6版本同一个类型的触发器只能有一个<em>(针对一个表)</em> <br>
  MySQL 5.7允许多个同一类型的触发器</p>
  
  <p><strong>注意：触发器只触发DML(Data Manipulation Language)操作，不会触发DDL(Data Definition Language)操作<em>（create,drop等操作）</em> </strong></p>
</blockquote></li>
<li><p><strong>创建触发器</strong></p>

<pre class="prettyprint hljs-dark"><code class="language-sql hljs"><span class="hljs-comment line-number">1.</span><span class="hljs-operator"><span class="hljs-keyword">CREATE</span><br><span class="hljs-comment line-number">2.</span>    [DEFINER = { <span class="hljs-keyword">user</span> | <span class="hljs-keyword">CURRENT_USER</span> }]<br><span class="hljs-comment line-number">3.</span>    <span class="hljs-keyword">TRIGGER</span> trigger_name  <span class="hljs-comment">-- 触发器名字</span><br><span class="hljs-comment line-number">4.</span>    trigger_time trigger_event  <span class="hljs-comment">-- 触发时间和事件</span><br><span class="hljs-comment line-number">5.</span>    <span class="hljs-keyword">ON</span> tbl_name <span class="hljs-keyword">FOR</span> <span class="hljs-keyword">EACH</span> <span class="hljs-keyword">ROW</span>    <br><span class="hljs-comment line-number">6.</span>    [trigger_order]<br><span class="hljs-comment line-number">7.</span>    trigger_body<br><span class="hljs-comment line-number">8.</span><br><span class="hljs-comment line-number">9.</span>trigger_time: { <span class="hljs-keyword">BEFORE</span> | <span class="hljs-keyword">AFTER</span> }   <span class="hljs-comment">-- 事件之前还是之后触发</span><br><span class="hljs-comment line-number">10.</span><br><span class="hljs-comment line-number">11.</span>trigger_event: { <span class="hljs-keyword">INSERT</span> | <span class="hljs-keyword">UPDATE</span> | <span class="hljs-keyword">DELETE</span> }  <span class="hljs-comment">-- 三个类型</span><br><span class="hljs-comment line-number">12.</span><br><span class="hljs-comment line-number">13.</span>trigger_order: { <span class="hljs-keyword">FOLLOWS</span> | <span class="hljs-keyword">PRECEDES</span> } other_trigger_name</span><br></code></pre>

<pre class="prettyprint hljs-dark"><code class="language-sql hljs"><span class="hljs-comment line-number">1.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> test_trigger_1 (<br><span class="hljs-comment line-number">2.</span>    -&gt;     <span class="hljs-keyword">name</span> <span class="hljs-built_in">varchar</span>(<span class="hljs-number">10</span>),<br><span class="hljs-comment line-number">3.</span>    -&gt;     score <span class="hljs-built_in">int</span>(<span class="hljs-number">10</span>),<br><span class="hljs-comment line-number">4.</span>    -&gt; primary <span class="hljs-keyword">key</span> (<span class="hljs-keyword">name</span>));</span><br><span class="hljs-comment line-number">5.</span>Query OK, 0 rows affected (0.14 sec)<br><span class="hljs-comment line-number">6.</span><br><span class="hljs-comment line-number">7.</span>    mysql&gt; delimiter //  <span class="hljs-comment">-- 将语句分隔符定义成 // （原来是';'）</span><br><span class="hljs-comment line-number">8.</span>    mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">create</span> <span class="hljs-keyword">trigger</span>  trg_upd_score  <span class="hljs-comment">-- 定义触发器名字</span><br><span class="hljs-comment line-number">9.</span>        -&gt; <span class="hljs-keyword">before</span> <span class="hljs-keyword">update</span> <span class="hljs-keyword">on</span> test_trigger_1  <span class="hljs-comment">-- 作用在test_trigger_1 更新(update)之前(before)</span><br><span class="hljs-comment line-number">10.</span>        -&gt; <span class="hljs-keyword">for</span> <span class="hljs-keyword">each</span> <span class="hljs-keyword">row</span>  <span class="hljs-comment">-- 每行</span><br><span class="hljs-comment line-number">11.</span>        -&gt; <span class="hljs-keyword">begin</span>  <span class="hljs-comment">-- 开始定义</span><br><span class="hljs-comment line-number">12.</span>        -&gt; <span class="hljs-keyword">if</span> <span class="hljs-keyword">new</span>.score &lt; <span class="hljs-number">0</span> <span class="hljs-keyword">then</span>   <span class="hljs-comment">-- 如果新值小于0</span><br><span class="hljs-comment line-number">13.</span>        -&gt;     <span class="hljs-keyword">set</span> <span class="hljs-keyword">new</span>.score=<span class="hljs-number">0</span>;</span>        <span class="hljs-comment">-- 则设置成0</span><br><span class="hljs-comment line-number">14.</span>        -&gt; elseif new.score &gt; 100 then  <span class="hljs-comment">-- 如果新值大于100</span><br><span class="hljs-comment line-number">15.</span>        -&gt;     <span class="hljs-operator"><span class="hljs-keyword">set</span> <span class="hljs-keyword">new</span>.score = <span class="hljs-number">100</span>;</span>  <span class="hljs-comment">-- 则设置成100</span><br><span class="hljs-comment line-number">16.</span>        -&gt; <span class="hljs-operator"><span class="hljs-keyword">end</span> <span class="hljs-keyword">if</span>;</span>  <span class="hljs-comment">-- begin对应的 结束 </span><br><span class="hljs-comment line-number">17.</span>        -&gt; <span class="hljs-operator"><span class="hljs-keyword">end</span>;</span>//   <span class="hljs-comment">-- 结束，使用新定义的 '//' 结尾</span><br><span class="hljs-comment line-number">18.</span>    Query OK, 0 rows affected (0.03 sec)<br><span class="hljs-comment line-number">19.</span><br><span class="hljs-comment line-number">20.</span>mysql&gt; delimiter ;  <span class="hljs-comment">-- 恢复 ';' 结束符</span><br><span class="hljs-comment line-number">21.</span><br><span class="hljs-comment line-number">22.</span><span class="hljs-comment">-- new.col : 表示更新以后的值</span><br><span class="hljs-comment line-number">23.</span><span class="hljs-comment">-- old.col : 表示更新以前的值(只读)</span><br><span class="hljs-comment line-number">24.</span><br><span class="hljs-comment line-number">25.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> test_trigger_1 <span class="hljs-keyword">values</span> (<span class="hljs-string">"tom"</span>, <span class="hljs-number">200</span>);</span>  <span class="hljs-comment">-- 插入新值</span><br><span class="hljs-comment line-number">26.</span>Query OK, 1 row affected (0.04 sec)<br><span class="hljs-comment line-number">27.</span><br><span class="hljs-comment line-number">28.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> test_trigger_1;</span><br><span class="hljs-comment line-number">29.</span>+<span class="hljs-comment">------+-------+</span><br><span class="hljs-comment line-number">30.</span>| name | score |<br><span class="hljs-comment line-number">31.</span>+<span class="hljs-comment">------+-------+</span><br><span class="hljs-comment line-number">32.</span>| tom  |   200 |  <span class="hljs-comment">-- 没改成100，因为定义的是update，而执行的是insert</span><br><span class="hljs-comment line-number">33.</span>+<span class="hljs-comment">------+-------+</span><br><span class="hljs-comment line-number">34.</span>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">35.</span><br><span class="hljs-comment line-number">36.</span>mysql&gt; <span class="hljs-keyword">update</span> test_trigger_1 <br><span class="hljs-comment line-number">37.</span>    -&gt; <span class="hljs-keyword">set</span> score=<span class="hljs-number">300</span> <span class="hljs-keyword">where</span> <span class="hljs-keyword">name</span>=<span class="hljs-string">'tom'</span>;</span> <span class="hljs-comment">-- 改成300</span><br><span class="hljs-comment line-number">38.</span>Query OK, 0 rows affected (0.00 sec)<br><span class="hljs-comment line-number">39.</span>Rows matched: 1  Changed: 0  Warnings: 0<br><span class="hljs-comment line-number">40.</span><br><span class="hljs-comment line-number">41.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> test_trigger_1;</span><br><span class="hljs-comment line-number">42.</span>+<span class="hljs-comment">------+-------+</span><br><span class="hljs-comment line-number">43.</span>| name | score |<br><span class="hljs-comment line-number">44.</span>+<span class="hljs-comment">------+-------+</span><br><span class="hljs-comment line-number">45.</span>| tom  |   100 | <span class="hljs-comment">-- 通过触发器的设置，大于100的值被修改成100</span><br><span class="hljs-comment line-number">46.</span>+<span class="hljs-comment">------+-------+</span><br><span class="hljs-comment line-number">47.</span>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)</span><br></code></pre></li>
</ul></div><div id="wmd-preview-section-footnotes" class="preview-content"></div></div></body></html>